﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SalePartial.Model;
using System.Data;
using System.Data.SqlClient;
using SalePartial.IDAL;
using SalePartial.Utility;

namespace SalePartial.SQLServerDAL
{
    class XPNewsComment : IXPNewsComment
    {
        public int Delete(Guid CoID)
        {
            string SqlString = "Delete XPNewsComment Where CoID = @CoID";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        Sql_Command.Parameters.AddWithValue("@CoID", CoID);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }

        public int DeleteDynamic(string v_whereCondition)
        {
            string SqlString = "Delete XPNewsComment Where " + v_whereCondition;
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;

                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }

        public int Insert(XPNewsCommentInfo info)
        {
            string SqlString = "Insert Into XPNewsComment Values " +
               "(@CoID,@NewsID,@Content,@CreateDate,@CreateTime,@UserIP)";
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString;
                        Sql_Command.Parameters.AddWithValue("@CoID", info.CoID);
                        Sql_Command.Parameters.AddWithValue("@NewsID", info.NewsID);
                        Sql_Command.Parameters.AddWithValue("@Content", info.Content);
                        Sql_Command.Parameters.AddWithValue("@CreateDate", info.CreateDate);
                        Sql_Command.Parameters.AddWithValue("@CreateTime", info.CreateTime);
                        Sql_Command.Parameters.AddWithValue("@UserIP", info.UserIP);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }

        public DataSet SelectAll()
        {
            throw new NotImplementedException();

        }

        public XPNewsCommentInfo SelectByCoID(Guid CoID)
        {
            XPNewsCommentInfo info = null;
            SqlDataReader reader = SelectReader(CoID);
            if (reader == null)
            {
                return null;
            }
            while (reader.Read())
            {
                info = new XPNewsCommentInfo(reader);

            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return info;

        }

        public SqlDataReader SelectByNewsID(Guid NewsID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPNewsComment");
            sb.Append("   WHERE ");
            sb.Append("  NewsID= @NewsID");

            try
            {
                SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString);

                Sql_Conn.Open();
                SqlCommand command = new SqlCommand(sb.ToString(), Sql_Conn) { CommandType = CommandType.Text };
                command.Parameters.AddWithValue("@NewsID", NewsID);
                return command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception e)
            {
                return null;
            }

        }

        public DataSet SelectDynamic(string v_whereCondition, string v_orderByExpression)
        {
            DataSet ds = new DataSet();

            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPNewsComment");
            if (v_whereCondition != null && v_whereCondition != string.Empty)
            {
                sb.Append("   WHERE ");
                sb.Append(v_whereCondition);
            }
            if (v_orderByExpression != null && v_orderByExpression != string.Empty)
            {
                sb.Append("   ORDER BY ");
                sb.Append(v_orderByExpression);
            }

            using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
            {

                using (SqlCommand Sql_Command = new SqlCommand())
                {
                    Sql_Conn.Open();


                    Sql_Command.Connection = Sql_Conn;
                    Sql_Command.CommandText = sb.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = Sql_Command;

                    da.Fill(ds);

                }
                Sql_Conn.Dispose();
                Sql_Conn.Close();
                Sql_Conn.Dispose();
            }
            return ds;

        }

        public DataSet SelectPaged()
        {
            throw new NotImplementedException();
        }

        public SqlDataReader SelectReader(Guid CoID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("  SELECT");
            sb.Append("  * ");
            sb.Append("  FROM");
            sb.Append("  XPNewsComment");
            sb.Append("   WHERE ");
            sb.Append("  CoID= @CoID");

            try
            {
                SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString);

                Sql_Conn.Open();
                SqlCommand command = new SqlCommand(sb.ToString(), Sql_Conn) { CommandType = CommandType.Text };
                command.Parameters.AddWithValue("@CoID", CoID);
                return command.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception e)
            {
                return null;
            }

        }

        public int Update(XPNewsCommentInfo info)
        {
            StringBuilder SqlString = new StringBuilder();
            SqlString.Append("Update  XPNewsComment set ");
            SqlString.Append("   NewsID = @NewsID   ");
            SqlString.Append("   , Content = @Content ");
            SqlString.Append("   , CreateDate = @CreateDate ");
            SqlString.Append("   , CreateTime = @CreateTime ");
            SqlString.Append("   , UserIP = @UserIP ");
            SqlString.Append("   Where CoID = @CoID ");
            try
            {
                using (SqlConnection Sql_Conn = new SqlConnection(GlobalSetting.ConnectionString))
                {

                    using (SqlCommand Sql_Command = new SqlCommand())
                    {
                        Sql_Conn.Open();

                        Sql_Command.Connection = Sql_Conn;
                        Sql_Command.CommandText = SqlString.ToString();
                        Sql_Command.Parameters.AddWithValue("@NewsID", info.NewsID);
                        Sql_Command.Parameters.AddWithValue("@Content", info.Content);
                        Sql_Command.Parameters.AddWithValue("@CreateDate", info.CreateDate);
                        Sql_Command.Parameters.AddWithValue("@CreateTime", info.CreateTime);
                        Sql_Command.Parameters.AddWithValue("@UserIP", info.UserIP);
                        Sql_Command.Parameters.AddWithValue("@CoID", info.CoID);
                        Sql_Command.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;

        }
    }
}
